IF EXISTS (SELECT * FROM sysobjects WHERE name = 'SaveSrvrInstncDatabaseSize' AND type = 'P')
     DROP PROCEDURE dbo.SaveSrvrInstncDatabaseSize
GO

CREATE PROCEDURE dbo.SaveSrvrInstncDatabaseSize
     @SrvrInstncDatabaseId int,
     @PackageExecutionId   bigint,
     @CaptureDt            datetime,
     @TotalDataSpaceMB     int,
     @TotalDataFiles       int,
     @TotalLogSpaceMB      int,
     @TotalLogFiles        int
AS
/*
 *
 * SaveSrvrInstncDatabaseSize
 *
 * Purpose:      
 *
 * Inputs:       
 *
 * Assumes:      
 *
 * Effects:      
 *
 * Returns:      
 *
 * Note(s):      
 *
 * CfgCtrl Keys: None
 *
 * Author(s):    NOVA Concepts Ltd.
 *
 * Change Log:   Initial Release, 10/20/2011, 1.00.0000
 *
 */
BEGIN
     SET NOCOUNT ON
     SET XACT_ABORT ON

     DECLARE @RC         int,
             @NOW        datetime,
             @TODAY      datetime,
             @MaxDt      datetime

     -- Get current date/time
     SELECT @NOW = @CaptureDt
     SELECT @TODAY = DATEADD( dd, DATEDIFF( dd, 0, @NOW ), 0 )

     -- Condition values
     SELECT @TotalDataSpaceMB = CASE WHEN @TotalDataSpaceMB < 1
                                     THEN 1
                                     ELSE @TotalDataSpaceMB
                                END,
            @TotalLogSpaceMB  = CASE WHEN @TotalLogSpaceMB < 1
                                     THEN 1
                                     ELSE @TotalLogSpaceMB
                                END

     -- Find out if we have the record already
     IF NOT EXISTS ( SELECT 'X'
                     FROM   dbo.SrvrInstncDatabaseSize 
                     WHERE  SrvrInstncDatabaseId = @SrvrInstncDatabaseId
                     AND    CollectionDateOnly   = @TODAY )
     BEGIN -- We do not have this record, so we will do an insert
          INSERT dbo.SrvrInstncDatabaseSize
               ( SrvrInstncDatabaseId,
                 PackageExecutionId,
                 CaptureDt,
                 CurrentRecordForDtFlag,
                 TotalDataSpaceMB,
                 TotalDataFiles,
                 TotalLogSpaceMB,
                 TotalLogFiles )
          SELECT @SrvrInstncDatabaseId,
                 @PackageExecutionId,
                 @NOW, -- CollectionDt
                 1, -- CurrentRecordForDtFlag
                 @TotalDataSpaceMB,
                 @TotalDataFiles,
                 @TotalLogSpaceMB,
                 @TotalLogFiles
     END -- End New insert
     ELSE
     BEGIN -- We have at least one copy of this record, so we may do an insert
          BEGIN TRANSACTION

          -- Get @MaxDt
          SELECT @MaxDt = CaptureDt
          FROM   dbo.SrvrInstncDatabaseSize 
          WHERE  SrvrInstncDatabaseId   = @SrvrInstncDatabaseId 
          AND    CollectionDateOnly     = @TODAY 
          AND    CurrentRecordForDtFlag = 1
          
          -- Reset current record for date flag to zero
          UPDATE dbo.SrvrInstncDatabaseSize 
          SET    CurrentRecordForDtFlag = 0
          WHERE  SrvrInstncDatabaseId   = @SrvrInstncDatabaseId 
          AND    CollectionDateOnly     = @TODAY 
          AND    CurrentRecordForDtFlag = 1

          -- Do regular checked insert
          INSERT dbo.SrvrInstncDatabaseSize
               ( SrvrInstncDatabaseId,
                 PackageExecutionId,
                 CaptureDt,
                 CurrentRecordForDtFlag,
                 TotalDataSpaceMB,
                 TotalDataFiles,
                 TotalLogSpaceMB,
                 TotalLogFiles )
          SELECT @SrvrInstncDatabaseId,
                 @PackageExecutionId,
                 @NOW, -- CaptureDt
                 1, -- CurrentRecordForDtFlag
                 @TotalDataSpaceMB,
                 @TotalDataFiles,
                 @TotalLogSpaceMB,
                 @TotalLogFiles
          FROM   dbo.SrvrInstncDatabaseSize
          WHERE  SrvrInstncDatabaseId = @SrvrInstncDatabaseId 
          AND    CaptureDt            = @MaxDt 
          -- Decide if there will be an insert
          AND  ( 1 = 0 -- Placeholder, always false, don't want to insert on accident
              OR ( @TotalDataSpaceMB != TotalDataSpaceMB )
              OR ( @TotalDataFiles   != TotalDataFiles )
              OR ( @TotalLogSpaceMB  != TotalLogSpaceMB )
              OR ( @TotalLogFiles    != TotalLogFiles )
               ) -- End of And-Or section

          SELECT @RC = @@ROWCOUNT 

          IF ( @RC = 1 )
          BEGIN -- We did do an insert so we commit it.
               COMMIT TRANSACTION
          END
          ELSE
          BEGIN -- We did not do any insert, this is not an error, but we will 
                -- do a rollback since we want to keep the last current record.
               ROLLBACK TRANSACTION
          END
     END -- End Existing Insert
END
GO

EXEC sp_addextendedproperty @name = N'MS_Description', @value = 'Save a server instance database size record if anything has changed from the previous run, calls to this sproc will NOT always generate a new record.', @level0type = N'Schema', @level0name = dbo, @level1type = N'PROCEDURE', @level1name = SaveSrvrInstncDatabaseSize;
GO
